<?php

namespace App\Http\Controllers\Admin\ExportOrigin;

use Dompdf\Exception;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Helper\Html as HtmlHelper;
use App\Admin\InputData;

class HuiBangJianCeController extends Controller
{
    private $wizard = null;
    private $initRowHeight = 14;
    /* 导出Excel 检测报告*/
    public function export(Request $request){
         //if (!isAdmin()) {
         //    return '修改升级中。。。。';
         //}
        $sampleId = $request->id;
        if($sampleId){ //如果是单样品原始记录直接导出
            $publicCon = new PublicController();
            return $publicCon->exportRes();
        }
        set_time_limit(0);
        ini_set('max_execution_time', 0);
        ini_set('memory_limit', '-1');
        $temp = './storage/ExcelTemplate/HuiBangJianCe/原始记录.xls'; //考虑到兼容Linux，斜杠用“/”
        if (!file_exists($temp)) {
            return '原始记录模板文件不存在';
        }
        $contract_id = $request->contract_id;
        // dd($contract_id);
        $batchNum = DB::table('a_contract')->where('id',$contract_id)->value('batchNum');
        $test_address = DB::table('a_contract')->where('id',$contract_id)->value('test_address');
        $gys = DB::table('a_contract_gaiyao as a')
            ->leftJoin('a_standard as c','a.std_id','=','c.id')
            ->where('a.contract_id',$contract_id)
            ->select('a.id','a.tool_id','a.tool_name','a.std_id','a.std_num','a.voltage','c.name as std_name','a.item_ids')
            ->get();
        if ($gys->count() == 0) {
            return "没有数据~";
        }
        // dd($gys);
        $headerContent = "&R批次编号：{$batchNum}"; //眉头内容
        $footerContent = '&L&12主检/日期 &C&12审核/日期 &R&12第&P页，共&N页'; //页脚内容
        $this->wizard = new HtmlHelper();
        $gys2 = [];
        foreach ($gys as $gy) {
            $gys2["{$gy->tool_id}][{$gy->std_id}"][] = $gy;
        }
        // dd($gys2,array_reverse($gys2));
        $gys2 = array_reverse($gys2);
        // dd($gys2);
        $item_datas = DB::table('a_item_data as a')->where('contract_id', $contract_id)->get();
        $gy_item_datas = $item_datas->groupBy('contract_gaiyao_id')->toArray();
        // $spreadsheet = new Spreadsheet();
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp); //读取模板文件做操作文件
        $spreadsheet->removeSheetByIndex(0);
        $testitems = DB::table('a_testitem')->pluck('name','id')->toArray();
        foreach ($gys2 as $key => $gys2V) {
            // if ($key == '9][2' || $key == '26][16' || $key == '11][2' || $key == '2][2' || $key == '20][2' || $key == '18][2') {
            //     continue ;
            // }
            
            list($tool_id,$std_id) = explode('][',$key);
            $gy1st = current($gys2V);
            $std = $gy1st->std_id;
            if(isset($spreadsheetTemp)){
                $spreadsheetTemp->disconnectWorksheets();
            }
            unset($spreadsheetTemp,$worksheetTemp);
            $spreadsheetTemp = \PhpOffice\PhpSpreadsheet\IOFactory::load($temp); //读取模板文件做取样文件
            $worksheetTemp = $spreadsheetTemp->getSheet(0);
            $worksheetTemp->setTitle("{$gy1st->tool_name}{$std}");
            $spreadsheet->addExternalSheet($worksheetTemp,0); //添加外部工作表
            $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet->getHeaderFooter()->setOddHeader($headerContent)->setEvenHeader($headerContent)->setOddFooter($footerContent)->setEvenFooter($footerContent);
            // dd($gys2V);
            
            $cellValues = [
                'A1'=>"（{$gy1st->tool_name}）原始记录",
                // 'DX2'=>$batchNum,
            ]; //初始化
            $rowIndex = 4; //定位到检测设备的第一行
            /*=================设备管理 start==================*/
                $stations = $this->getStations($gys2V,$gy_item_datas);
                $stationsCount = count($stations);
                $colFields = [
                    'R'=>'name',
                    'BG'=>'dev_num',
                    'CT'=>'trace_exp_date',
                ];
                $cellValues += $this->getSourceCellValues($worksheet,$rowIndex,$stations,$colFields);
            /*=================设备管理 end==================*/
            $rowIndex += $stationsCount ?: 1; //定位到检测设备的最后一行+1
            $rowIndexjia = $rowIndex+1;
            $cellValues["R{$rowIndex}"] = $test_address; //检测地点
            $cellValues["R{$rowIndexjia}"] = "{$gy1st->std_num} 《{$gy1st->std_name}》"; //检测依据
            $rowIndex += 3; //定位到检测标准技术要求的第一行
            /*=================检测标准 技术要求 start==================*/
                $rels = $this->getYaoqius($gy1st);
                
                $colFields = [
                    'R'=>'item_name',
                    'AD'=>[
                        'name'=>'jishu_yaoqiu',
                        'wrapNum'=>114 //一行占用的字节数，按2个字节算1个字计算
                    ],
                ];
                // dump($cellValues);
                $cellValues += $this->getSourceCellValues($worksheet,$rowIndex,$rels,$colFields);
                // dd($rels,$cellValues);
            /*=================检测标准 技术要求 end==================*/
            $rowIndex += $rels->count() ?: 1; //定位到检测标准 技术要求的最后一行+1
            $worksheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd($rowIndex,$rowIndex+1)->setPaperSize(9);
            $rowIndex += 2; //定位到检测样品的第一行
            /*=================检测样品 start==================*/
                $samples = $this->getSamples($gys2V);
                $itemIdss = array_column($samples,'item_ids');
                $count2 = 0;
                foreach($itemIdss as $itemIds){
                    $count = count(explode(',', $itemIds));
                    $count2 += $count;
                }
                $worksheet->insertNewRowBefore($rowIndex,$count2-1); //一次性插入，分开会占用太多
                for ($i=0; $i < $count2; $i++) { 
                    $worksheet->getRowDimension($rowIndex+$i)->setRowHeight($this->initRowHeight);
                }
                foreach($samples as $k => $sample){
                    $itemIds = explode(',', $sample->item_ids);
                    $count = count($itemIds);
                    $lastRowIndex = $rowIndex+$count-1;
                    $mergeCells = [
                        "A{$rowIndex}:I{$lastRowIndex}",
                        "J{$rowIndex}:S{$lastRowIndex}",
                        "T{$rowIndex}:AC{$lastRowIndex}",
                        // "DL{$rowIndex}:DW{$lastRowIndex}",
                        "DX{$rowIndex}:EI{$lastRowIndex}",
                    ];
                    $startRow = $rowIndex;
                    for ($i=0; $i < $count; $i++) { 
                        $mergeCellsI = [
                            "AD{$startRow}:AP{$startRow}",
                            "AQ{$startRow}:BZ{$startRow}",
                            "CA{$startRow}:CL{$startRow}",
                            "CM{$startRow}:DK{$startRow}",
                            "DL{$startRow}:DW{$startRow}",
                        ];
                        $mergeCells = array_merge($mergeCells,$mergeCellsI);
                        $startRow += 1;
                    }

                    // dd($gys2V,$mergeCells);
                    foreach ($mergeCells as $mergeCell) {
                        $worksheet->mergeCells($mergeCell);
                    }
                    $barCode = str_replace($batchNum,'',$sample->bar_code).'#';
                    $cellValues["A{$rowIndex}"] = $barCode;
                    $cellValues["J{$rowIndex}"] = $sample->voltage; //额定电压
                    $cellValues["T{$rowIndex}"] = ['value'=>$sample->class,'wrapNum'=>10,'mergeRowCount'=>$count]; //分类
                    // $cellValues["DL{$rowIndex}"] = $sample->test_time;
                    $cellValues["DX{$rowIndex}"] = $sample->res == 1 ?'合格': ($sample->res == 2 ?'不合格':'');
                    // $inputs = DB::table('a_input_data')->where(['sample_info_id'=>$sample->id])->get()->keyBy('testdata_key_id')->toArray();
                    $inputs = InputData::with('input_data_log:input_data_id,val')->where(['sample_info_id'=>$sample->id])->get()->keyBy('testdata_key_id')->toArray();
                    // dd($inputs);
                    foreach($itemIds as $ii => $itemId){
                        $itemName = data_get($testitems,$itemId,'');
                        $cellValues["AD{$rowIndex}"] = ['value'=>$itemName,'wrapNum'=>12]; //检测项目
                        $itemData = DB::table('a_item_data')->where(['sample_info_id'=>$sample->id,'item_id'=>$itemId])->orderBy('id','desc')->first();
                        // dd($itemData);
                        if(!$itemData){
                            continue;
                        }
                        $cellValues["DL{$rowIndex}"] = substr( $itemData->utime, 0,10);
                        // 结论,0-不符合，1-符合，2-未录入,3-未试验
                        $res = $itemData->res;
                        switch($res){
                            case 0:
                                $res = '不符合';
                                break;
                            case 1:
                                $res = '符合';
                                break;
                            default:
                                // if($itemData->item_id == '61' || $itemData->item_id == '84'){
                                //     $res = '通过';
                                // }else{
                                    
                                // }
                                $res = '';
                                
                        }
                        $cellValues["CA{$rowIndex}"] =  $res; //检测结果
                        // dd($itemData);
                        $envInfo = $res ? ($itemData->item_id == 61 || $itemData->item_id == 84 ? '/' :$itemData->temp.'/'.$itemData->hum.'/'.$itemData->pressure.'/'.$itemData->wea) : ''; //弱国未检测环境信息和检测数据也为空
                        $cellValues["CM{$rowIndex}"] = $envInfo; //环境信息,温度/湿度/大气压力/天气
                    //   dd($envInfo);
                        if($gy1st->tool_name == '施工机具'){
                            $sample->std_id = 4; //DL/T 875-2016 标准以为ID为4,实验软件上默认也为4ID,后面平台上修改了，试验软件没有修改，所以还在传4过来
                        }
                        $keys = DB::table('a_testdata_key')
                            ->where([
                                'std_id'=>$sample->std_id,
                                'tool_id'=>$sample->tool_id,
                                'item_id'=>$itemId,
                            ])->orderBy('sort','asc')
                            ->select('id','data_key','data_unit')->get();
                        $testStr = '';
                        if ($res) {
                            // dd($res,$keys,$itemId);
                            
                            foreach ($keys as $key) {
                                $input = data_get($inputs,$key->id,[]);
                                if ($input) { //因为有的标题可能隐藏了没用到，所以这里判断下
                                    $testStr .= $key->data_key.'：';
                                    $inputDataLog = $input['input_data_log'];
                                    $logVals = array_column($inputDataLog, 'val');
                                    foreach ($logVals as $k => $logVal) {
                                        // $testStr .= '<del>'.$logVal.'</del> ';
                                    }
                                    $testStr .= $input['val'].'('.$key->data_unit.'); ';
                                }
                                
                            }
                            
                            $remark = $itemData->remark;
                            if ($remark && $remark != '无') {
                                $testStr .= "{$remark} ";
                            }
                            if(($itemId == '61' || $itemId == '84') && empty($testStr)){
                                $testStr .= '通过';
                            }
                        }
                        $testStr = trim($testStr);
                        $testStr = trim($testStr,';');
                        // dd($testStr);
                        // if(){}
                        $cellValues["AQ{$rowIndex}"] = ['value'=>$testStr,'wrapNum'=>36]; //检测数据
                        $rowIndex += 1;
                    }
                    // dd($itemIds,$sample,$gys2V);
                }
                // $remarkStr = $this->getRemarkStr($remarkAry);
                // $cellValues["R{$rowIndex}"] = ['value'=>trim($remarkStr),'wrapNum'=>124];
            /*=================检测样品 end==================*/
            // dd($cellValues);
            $this->setCellValues($worksheet,$cellValues); //给当前工器具数据赋值
            // dd($worksheet->getPageSetup());
        }

        $spreadsheetTemp->disconnectWorksheets();
        unset($spreadsheetTemp,$worksheetTemp);
        // return PublicController::saveDown($spreadsheet,'test.xls');
        /*=================样品信息 start==================*/
            $worksheet = $spreadsheet->getSheetByName('样品信息');
            $samples = DB::table('a_sample_info as a')
                ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
                ->where('b.contract_id',$contract_id)
                ->orderBy('a.bar_code','asc')
                ->select('b.tool_name','a.bar_code','a.self_num','a.guige_xinghao','b.voltage','a.sample_status','a.brand','a.manufacturer','a.production_date')
                ->get();
            $rowIndex = 3;
            $cellValues = [];
            $count = $samples->count();
            if ($count > 1) {
                $worksheet->insertNewRowBefore($rowIndex,$count-1);
                for ($i=0; $i < $count; $i++) { 
                    $worksheet->getRowDimension($rowIndex+$i)->setRowHeight($this->initRowHeight);
                }
            }
            foreach ($samples as $k => $sample) {
                $barCode = str_replace($batchNum,'',$sample->bar_code).'#';
                $cellValues["A{$rowIndex}"] = $sample->tool_name;
                $cellValues["B{$rowIndex}"] = $barCode;
                $cellValues["C{$rowIndex}"] = $sample->bar_code;
                $cellValues["D{$rowIndex}"] = $sample->self_num;
                $cellValues["E{$rowIndex}"] = $sample->guige_xinghao;
                $cellValues["F{$rowIndex}"] = $sample->voltage;
                $cellValues["G{$rowIndex}"] = $sample->sample_status;
                $cellValues["H{$rowIndex}"] = $sample->brand;
                $cellValues["I{$rowIndex}"] = ['value'=>$sample->manufacturer,'wrapNum'=>30];
                $cellValues["J{$rowIndex}"] = $sample->production_date;
                $rowIndex += 1;
            }

            $this->setCellValues($worksheet,$cellValues);

            /*设置页眉*/
            // $headerContent = ; //眉头内容
            $setting = getCache('setting');
            $company_name = $setting['setting']->name_rep;
            $report_num_pre = $setting['setting']->report_num_pre;
            $headerContent = "&L{$company_name}     {$report_num_pre}{$batchNum}";
            // $worksheet->getHeaderFooter()->setOddHeader($headerContent);
        /*=================样品信息 end==================*/
        // dd($spreadsheet);
        $is_save = isset($request->is_save) ?$request->is_save:0;
        if($is_save == 1){
            $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
//          $writer->setPreCalculateFormulas(false); //禁用公式预先计算
            $save_path = 'savefile/'.$batchNum.'/原始记录'.$batchNum.'.xls';
            $disk = Storage::disk('public');
            $disk->put($save_path,''); //创建路径
            $writer->save($disk->path($save_path));//将Excel文件保存在服务器的磁盘上
            //为了防止内存泄露，建议用完手动清理
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
          //  die;
        }else{
            //----------直接在浏览器下载 start
            $file_name = "原始记录-批次{$batchNum}.xls"; //输出的文件名称
            try{
                return PublicController::saveDown($spreadsheet,$file_name);
            }catch(\Exception $exception){
                return $exception->getMessage();
            }
        }
        
            
        //----------直接在浏览器下载----end
    }

    private function getRemarkStr($ary){
        $zu = [];
        $start = $end = '';
        foreach ($ary as $k => $item) {
            list($bar_code,$remark) = $item;
            $bar_code = trim($bar_code,'#');
            if ($start == '') {
                $start = $bar_code;
            }
            $next = next($ary);
            list($bar_code2,$remark2) = $next;
            $bar_code2 = trim($bar_code2,'#');
            if($bar_code + 1 == $bar_code2 && $remark == $remark2 ){
                $end = $bar_code2;
                $zu[$start] = [$end,$remark];
            }else{
                $start = $bar_code2;
            }
        }
        $str = '';
        foreach ($zu as $start => $item) {
            list($end,$remark) = $item;
            $str .= "{$start}#-{$end}#：{$remark}； ";
        }
        return $str;
    }

    private function getSamples($gys2V){
        $samples2 = [];
        foreach($gys2V as $gys2VV){
            $samples = DB::table('a_sample_info as a')
                ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
                ->where('a.contract_gaiyao_id',$gys2VV->id)
                ->orderBy('a.bar_code','asc')
                ->select('a.id','a.bar_code','a.res','a.test_time','b.voltage','b.item_ids','b.std_id','b.tool_id','b.class')
                ->get()->toArray();
            $samples2 = array_merge($samples2,$samples);
        }
        // dd($gys2V);
        return $samples2;
    }

    //获取数据表中指定行的合并单元
    private function getRowMergeCells($worksheet,$rowIndex){
        $mergeCells = $worksheet->getMergeCells();
        // dd($mergeCells);
        $rowMergeCells = [];
        foreach ($mergeCells as $mergeCell) {
            list($mergeCell0,$mergeCell1) = explode(':',$mergeCell);
            for($i=0;$i<strlen($mergeCell0);$i++){
                if(is_numeric($mergeCell0[$i])){
                    if (substr($mergeCell0, $i) == $rowIndex) {
                        $rowMergeCells[] = $mergeCell;
                    }
                    break;
                }
            }
        }
        return $rowMergeCells;
    }

    private function getYaoqius($gy1st){
        return DB::table('a_std_tool_item_rel as a')
            ->leftJoin('a_testitem as b','a.testitem_id','=','b.id')
            ->where(['std_id'=>$gy1st->std_id,'tool_id'=>$gy1st->tool_id])
            ->where('a.ishide',0)
            ->select('a.jishu_yaoqiu','b.name as item_name','a.sort')
            ->orderBy('a.sort','asc')->get();
    }

    //获取指定资源的指定单元的指定内容
    private function getSourceCellValues($worksheet,$rowIndex,$sources,$colFields){
        $initRowIndex = $rowIndex;
        $rowMergeCells = $this->getRowMergeCells($worksheet,$initRowIndex);
        // dd($initRowIndex,$rowMergeCells);
        $count = count($sources);
        $cellValues = [];
        if ($count > 0) {
            if ($count > 1) {
                $worksheet->insertNewRowBefore($rowIndex,$count-1);
            }
            for ($i=0; $i < $count; $i++) {
                if ($i < $count) {
                    $MergeCells = [];
                    foreach ($rowMergeCells as $rowMergeCell) {
                        $MergeCells[] = str_replace($initRowIndex, $rowIndex, $rowMergeCell);
                    }
                    foreach ($MergeCells as $MergeCell) {
                        $worksheet->mergeCells($MergeCell);
                    }
                }
                $source = $sources[$i];

                foreach ($colFields as $col => $field) {
                    if (is_array($field)) {
                        $field['value'] = data_get($source,$field['name'],'');
                        unset($field['name']);
                        $cellValues[$col.$rowIndex] = $field;
                    }else{
                        $cellValues[$col.$rowIndex] = data_get($source,$field,'/');
                    }
                }
                $rowIndex += 1;
            }
        }else{
            foreach ($colFields as $col => $field) {
                $cellValues[$col.$rowIndex] = '/';
            }
        }
        return $cellValues;
    }

    private function getStations($gys,$gy_item_datas){
        $station_ids2 = [];
        foreach($gys as $gy){
            /*获取当前概要下 station_ids*/
            foreach ($gy_item_datas[$gy->id] as $gy_item_data) {
                $gy_item_data_station_ids = $gy_item_data->station_ids;
                if ($gy_item_data_station_ids) {
                    $gy_item_data_station_ids = explode(',',trim($gy_item_data_station_ids,','));
                    $count = count($gy_item_data_station_ids);
                    $item_data_ids2 = [];
                    for ($i=0; $i < $count; $i++) { 
                        $item_data_ids2[] = $gy_item_data->id;
                    }
                    $station_ids2Val = array_combine($gy_item_data_station_ids, $item_data_ids2);
                    $station_ids2 += $station_ids2Val; //array_merge会把key去掉，这里直接用+
                }
            }
        }
        // dd($station_ids2 );
        $stations = [];
        foreach ($station_ids2 as $station_id => $item_data_id) {
            $station = DB::table('a_item_data_station')->where(['item_data_id'=>$item_data_id,'station_id'=>$station_id])->first();
            //因为a_item_data_station记录是后期根据需求加的，所以这里如果找不到直接去找源数据
            $station = $station ?: DB::table('a_station')->where(['id'=>$station_id])->first();
            if($station){
                $stations[] = $station;
            }
        }

        
        return $stations;
    }

    private function setCellValues($worksheet,$cellValues){
        $wizard = $this->wizard;
        foreach ($cellValues as $cell => $value) {
            if (is_array($value)) {
                $value = $value['value'];
                $len = (strlen($value) + mb_strlen($value,"UTF8"))/2;
                for($i=0;$i<strlen($cell);$i++){
                    if(is_numeric($cell[$i])){
                        $rowIndex = substr($cell, $i);
                        break;
                    }
                }
                $item = $cellValues[$cell];
                $warpNum = array_get($item,'wrapNum',false); //是否限制一行占用多少字符
                if ($warpNum && $len > $warpNum) {
                    $height2 = ceil($len/$warpNum)*$this->initRowHeight;
                    $mergeRowCount = array_get($item,'mergeRowCount',false); //是否限制一栏占用几行
                    if ($mergeRowCount) {
                        $height = $this->initRowHeight * ($mergeRowCount-1); //计算其他项目的默认行高
                        $height2 -= $height;
                    }
                    //计算本来的行高，并对比，如果要设置的行高比之前的行高高，则重新设置
                    $oldHeight = $worksheet->getRowDimension($rowIndex)->getRowHeight();
                    if ($height2 > $oldHeight) {
                        $worksheet->getRowDimension($rowIndex)->setRowHeight($height2);
                    }
                }
            }
            if (strstr($value,'<del>')) {
                $value = $wizard->toRichTextObject($value);
            }
            // dump($cell.'=>'.$value);
            $worksheet->setCellValue($cell,$value ?: '/');
        }
    }
}
